﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using TeaLight.Core.BL;
using TeaLight.Core.BL.Entities;
using TeaLight.DL.SQLite;

namespace TeaLight.Core.DL
{
    /// <summary>
    /// TaskDatabase builds on SQLite.Net and represents a specific database, in our case, the TeaLight DB.
    /// It contains methods for retreival and persistance as well as db creation, all based on the 
    /// underlying ORM.
    /// </summary>
    public class Database : SQLiteConnection
    {
        protected static Database me = null;
        protected static string dbLocation;

        static object locker = new object();

        /// <summary>
        /// Initializes a new instance of the <see cref="Database"/> TeaLightDatabase. 
        /// if the database doesn't exist, it will create the database and all the tables.
        /// </summary>
        /// <param name='path'>
        /// Path.
        /// </param>
        protected Database(string path)
            : base(path)
        {
            // create the tables
            CreateTable<Session>();
            CreateTable<Speaker>();

            // FK
            CreateTable<SessionSpeaker>();

        }

        static Database()
        {
            // set the db location
            dbLocation = DatabaseFilePath;

            // instantiate a new db
            me = new Database(dbLocation);
        }

        public static string DatabaseFilePath
        {
            get
            {
#if SILVERLIGHT
                var path = "TeaLightDB.db3";
#else

#if __ANDROID__
            string libraryPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); ;
#else
			// we need to put in /Library/ on iOS5.1 to meet Apple's iCloud terms
			// (they don't want non-user-generated data in Documents)
			string documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal); // Documents folder
			string libraryPath = Path.Combine (documentsPath, "../Library/");
#endif
			var path = Path.Combine (libraryPath, "TeaLightDB.db3");
#endif
                return path;
            }
        }

        public static IEnumerable<T> GetItems<T>() where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                return (from i in me.Table<T>() select i).ToList();
            }
        }

        public static T GetItem<T>(int id) where T : IBusinessEntity, new()
        {
            lock (locker)
            {

                // ---
                //return (from i in me.Table<T> ()
                //        where i.ID == id
                //        select i).FirstOrDefault ();

                // +++ To properly use Generic version and eliminate NotSupportedException
                // ("Cannot compile: " + expr.NodeType.ToString ()); in SQLite.cs
                return me.Table<T>().FirstOrDefault(x => x.Id == id);
            }
        }

        public static TableQuery<T> GetTable<T>() where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                return me.Table<T>();
            }
        }

        public static int SaveItem<T>(T item) where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                
                if (item.Id != 0
                    && !(GetItem<T>(item.Id) == null))
                {
                    me.Update(item);
                    return item.Id;
                }
                else
                {
                    return me.Insert(item);
                }
            }
        }

        public static void SaveItems<T>(IEnumerable<T> items) where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                me.BeginTransaction();

                foreach (T item in items)
                {
                    SaveItem<T>(item);
                }

                me.Commit();
            }
        }

        public static int DeleteItem<T>(int id) where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                return me.Delete<T>(new T() { Id = id });
            }
        }

        public static void ClearTable<T>() where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                me.Execute(string.Format("delete from \"{0}\"", typeof(T).Name));
            }
        }

        // helper for checking if database has been populated
        public static int CountTable<T>() where T : IBusinessEntity, new()
        {
            lock (locker)
            {
                string sql = string.Format("select count (*) from \"{0}\"", typeof(T).Name);
                var c = me.CreateCommand(sql, new object[0]);
                return c.ExecuteScalar<int>();
            }
        }

    }
}